﻿using Business.Base.DB;
using Business.Base.DB.DBRules;
using Business.Base.DB.Query;
using Business.CentrifugalPump.Perf;
using Business.NPSH;
using Business.ScrewPump.Perf;
using Configuration;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Business.Base.DB
{
    class DBManager: IDBManager
    {
        private String fields;
        private String aValues;
        private String table;
        private Type type;
        private Dictionary<string, string> fieldMap;

        SqlConnection connection = null;

        public static Dictionary<string, DBManager> dbManagerDic = new Dictionary<string, DBManager>();





        static DBManager()
        {

            Type[] types = new Type[]
                {  typeof(CentrifugalPumpPerfExperiment),
                   typeof(CentrifugalPumpPerfExperimentRecord),
                   typeof(ScrewPumpPerfExperiment),
                   typeof(ScrewPumpPerfExperimentRecord),
                   typeof(NpshExperiment),
                   typeof(NpshExperimentRecord)

                };
            foreach (Type type in types)
            {
                dbManagerDic.Add(type.Name, new DBManager(type));
            }          
        }




        private SqlCommand sqlSelectCountCommand = null;
        private SqlCommand sqlSelectCommand = null;
        private SqlDataAdapter sqlSelectAdapter = null;
        private SqlCommand sqlInsertCommand = null;
        private SqlCommand sqlDeleteCommand = null;
        private SqlCommand sqlUpdateCommand = null;

        DBManager(Type type)
        {
            DBMapping dbMapping = DBMappingRules.mappingDic[type.Name];
            
            Dictionary<string, string> dic = dbMapping.Mapping;
            IEnumerator enumerator = dic.Keys.GetEnumerator();
            StringBuilder sbFileds = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            List<string> keys = new List<string>();
            int counter = 0;
            while (enumerator.MoveNext())
            {
                String key = (String)enumerator.Current;
                keys.Add(key);
                if (counter == 0)
                {
                    sbFileds.Append(key);
                    sbValues.Append("@" + dic[key]);
                }
                else
                {
                    sbFileds.Append("," + key);
                    sbValues.Append(",@" + dic[key]);
                }
                counter++;
            }

            this.type = type;
            this.table = dbMapping.TableName;
            this.fields = sbFileds.ToString();
            this.aValues = sbValues.ToString();
            this.fieldMap = dic;


            String connectionString = DataSourceConfig.DataSourceString;
            connection = new SqlConnection(connectionString);

            sqlSelectCountCommand = new SqlCommand();
            sqlSelectCountCommand.Connection = connection;

            sqlSelectCommand = new SqlCommand();
            sqlSelectCommand.Connection = connection;

            sqlInsertCommand = new SqlCommand();
            sqlInsertCommand.Connection = this.connection;
            sqlInsertCommand.CommandText = "INSERT INTO [" + table + "] (" + fields + ") VALUES (" + aValues + ") SELECT  @@IDENTITY  as  'bh'";

            sqlDeleteCommand = new SqlCommand();
            sqlDeleteCommand.Connection = this.connection;
            sqlDeleteCommand.CommandText = "delete from " + table;

            sqlUpdateCommand = new SqlCommand();
            sqlUpdateCommand.Connection = this.connection;


            sqlSelectAdapter = new SqlDataAdapter();
            sqlSelectAdapter.SelectCommand = sqlSelectCommand;
        }


        /*public DBManager(String tableName, Dictionary<string,string> dic)
        {
            IEnumerator enumerator = dic.Keys.GetEnumerator();
            StringBuilder sbFileds = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            List<string> keys = new List<string>();
            int counter = 0;
            while (enumerator.MoveNext())
            {
                String key = (String)enumerator.Current;
                keys.Add(key);
                if (counter == 0)
                {
                    sbFileds.Append(key);
                    sbValues.Append("@"+dic[key]);
                }
                else
                {
                    sbFileds.Append("," + key);
                    sbValues.Append(",@" + dic[key]);
                }
                counter++;
            }
 
            this.table = tableName;
            this.fields = sbFileds.ToString();
            this.aValues = sbValues.ToString();
            this.fieldMap = dic;


            String connectionString = DataSourceConfig.DataSourceString;
            connection = new SqlConnection(connectionString);

            sqlSelectCountCommand = new SqlCommand();
            sqlSelectCountCommand.Connection = connection;

            sqlSelectCommand = new SqlCommand();
            sqlSelectCommand.Connection = connection;

            sqlInsertCommand = new SqlCommand();
            sqlInsertCommand.Connection = this.connection;
            sqlInsertCommand.CommandText = "INSERT INTO [" + table + "] (" + fields + ") VALUES (" + aValues + ") SELECT  @@IDENTITY  as  'bh'";

            sqlDeleteCommand = new SqlCommand();
            sqlDeleteCommand.Connection = this.connection;
            sqlDeleteCommand.CommandText = "delete from " + table;

            sqlUpdateCommand = new SqlCommand();
            sqlUpdateCommand.Connection = this.connection;


            sqlSelectAdapter = new SqlDataAdapter();
            sqlSelectAdapter.SelectCommand = sqlSelectCommand;
        }
        */
  

        public int GetCount(BaseQuery query)
        {
            sqlSelectCountCommand.CommandText = "select count(*) from " + table + query.ToSQLConditionWithOutSort();
            connection.Open();
            object result = sqlSelectCountCommand.ExecuteScalar();
            connection.Close();
            return (int)result;     

        }

        private String getPageQuery(BaseQuery query)
        {
            String pageQuery = " offset " + (query.PageNum - 1) * query.PageSize + " rows fetch next " + query.PageSize + " rows only;";
            return pageQuery;
        }

        DataTable GetDataList(BaseQuery query)
        {
            sqlSelectCommand.CommandText = "select Id," + this.fields + " from " + this.table + query.ToSQLConditionString() + this.getPageQuery(query);
            DataTable dataTable = new DataTable();
            sqlSelectAdapter.Fill(dataTable);
            return dataTable;
        }

        private void assignValue( DataRow row, ref Object obj)
        {
            Type type = obj.GetType();
            PropertyInfo[] properties = type.GetProperties(System.Reflection.BindingFlags.Public);
            Dictionary<string, string> dic = new Dictionary<string, string>();
            
            foreach (PropertyInfo propertyInfo in properties)
            {
                if (!propertyInfo.CanWrite)
                {
                    continue;
                }
                DBFieldAttribute dbFieldAttribute = propertyInfo.GetCustomAttribute<DBFieldAttribute>();
                string name = dbFieldAttribute.FiledName;
                propertyInfo.SetValue(obj, row[name]);
            }
        }

        public List<Object> Query(BaseQuery query)
        {
            DataTable table =  this.GetDataList(query);
            List<Object> list = new List<object>();
            string name = type.FullName;
           
            foreach (DataRow row in table.Rows)
            {
                Assembly assembly = Assembly.GetExecutingAssembly(); // 获取当前程序集
                Object obj = assembly.CreateInstance(name);
                assignValue(row, ref obj);
                list.Add(obj);
            }
            return list;
        }

        private int InertMap(Dictionary<String, Object> fieldMap)
        {
            sqlInsertCommand.Parameters.Clear();

            foreach (KeyValuePair<String, object> kv in fieldMap)
            {
                sqlInsertCommand.Parameters.Add(new SqlParameter("@"+kv.Key, kv.Value));
            }
            connection.Open();
            Decimal id = (Decimal)sqlInsertCommand.ExecuteScalar();       
            // sqlInsertCommand.ExecuteNonQuery();
            connection.Close();
            
            return (int)Convert.ToInt32(Convert.ToDouble(id)); 

        }

   
        public int Insert(object obj)
        {
            sqlInsertCommand.Parameters.Clear();

            string[] fieldNames
                = this.fields.Split(new char[] { ',' });
            Dictionary<String, Object> fieldValueMap = new Dictionary<string, object>();
            

            IEnumerator enumerator = fieldMap.Values.GetEnumerator();
    
            while (enumerator.MoveNext())
            {
                String propertyName = (String)enumerator.Current;
                try
                {
                    Type Ts = obj.GetType();
                    //Console.WriteLine(propertyName + "----");
                    object o = Ts.GetProperty(propertyName).GetValue(obj, null);
                    //Console.WriteLine(propertyName + " " + o.ToString());
                    if (null == o)
                        continue;
                    fieldValueMap.Add(propertyName, o);
                   

                }
                catch (Exception e)
                {
                    throw e;
                }
            }

          
            return InertMap(fieldValueMap);
        

        }

        public void Delete(Object id)
        {
            if (id != null)
            {
                this.sqlDeleteCommand.CommandText = "delete from " + table + " where id = @id";
                sqlDeleteCommand.Parameters.Add(new SqlParameter("@id", id));
            }
            connection.Open();
            sqlDeleteCommand.ExecuteNonQuery();
            sqlDeleteCommand.Parameters.Clear();
            connection.Close();
        }


        public void DeleteByCondtion(string condition)
        {
            this.sqlDeleteCommand.CommandText = "delete from " + table +" where " + condition;
            connection.Open();
            sqlDeleteCommand.ExecuteNonQuery();
            sqlDeleteCommand.Parameters.Clear();
            connection.Close();
        }

        public void Update(int id, object obj)
        {
            StringBuilder sb = new StringBuilder();
            sqlUpdateCommand.Parameters.Clear();

            sb.Append("update "+ table+" set ");
            

            List<string> keys =  this.fieldMap.Keys.ToList();
            int counter = 0;

            for (int i = 0; i < keys.Count; i++)
            {
                String propertyName = this.fieldMap[keys[i]];
                try
                {
                    Type Ts = obj.GetType();
                    //Console.WriteLine(propertyName + "----");
                    object o = Ts.GetProperty(propertyName).GetValue(obj, null);
                    //Console.WriteLine(propertyName + " " + o.ToString());
                    if (null == o)
                    {
                        counter++;
                        continue;
                    }
                       
                    if (i == (keys.Count-1))
                    {
                        sb.Append(keys[i] + "=" + "@" + keys[i]);
                    }
                    else
                    {
                        sb.Append(keys[i] + "=" + "@" + keys[i] + ",");
                    }
                    sqlUpdateCommand.Parameters.AddWithValue("@" + keys[i], o);
                }
                catch (Exception e)
                {
                    throw e;
                }


            }
            sb.Append(" where id = " + id);
            //更新的对象所有属性都是空的！
            if (counter == keys.Count)
            {
                throw new Exception();
            }
  
        }
    }
}

